package com.sadam.ui4.Data;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import androidx.annotation.Nullable;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class MySqLiteOpenHelper extends SQLiteOpenHelper {
    // 数据库名称
    public static final String DATABASE_NAME = "UI4_DATABASE";
    public static final int DATABASE_VERSION = 20;
    // 三张数据表的表名
    private static final String TABLE_NAME_Note = "Note";
    private static final String TABLE_NAME_User = "User";
    private static final String TABLE_NAME_Video = "Video";
    private static final String TABLE_NAME_COMMENT = "Comment";
    private static final String TABLE_NAME_LIKE = "LikeTable";

    /**
     * 创建 Note 表的 SQL 语句
     * 包含：id（主键自增）、content（内容）、user_id（外键）、changedTime（修改时间戳）、isDeleted（软删除标记）
     */
    private static final String CREATE_Note = "CREATE TABLE \"" + TABLE_NAME_Note + "\" (\n" +
            "\t\"id\"\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
            "\t\"content\"\tTEXT,\n" +
            "\t\"user_id\"\tINTEGER NOT NULL,\n" +
            "\t\"changedTime\"\tINTEGER NOT NULL,\n" +
            "\t\"isDeleted\"\tINTEGER NOT NULL\n" +
            ");";

    /**
     * 创建 User 表的 SQL 语句
     * 包含：id（主键自增）、name（用户名，唯一）、password（密码）、isDeleted（软删除标记）
     */
    private static final String CREATE_User = "CREATE TABLE \"" + TABLE_NAME_User + "\" (\n" +
            "\t\"id\"\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
            "\t\"name\"\tTEXT NOT NULL UNIQUE,\n" +
            "\t\"password\"\tTEXT NOT NULL,\n" +
            "\t\"isDeleted\"\tINTEGER NOT NULL,\n" +
            "\t\"selfIntroduction\"\tTEXT,\n" +
            "\t\"avatarPath\"\tTEXT\n" +        // 新增头像路径字段
            ");";

    /**
     * 创建 Video 表的 SQL 语句
     */
    private static final String CREATE_Video = "CREATE TABLE \"" + TABLE_NAME_Video + "\" (\n" +
            "\t\"id\"\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
            "\t\"user_id\"\tINTEGER NOT NULL,\n" +
            "\t\"videoFilePath\"\tTEXT NOT NULL,\n" +
            "\t\"title\"\tTEXT,\n" +
            "\t\"introduction\"\tTEXT,\n" +
            "\t\"label\"\tTEXT," +
            "\t\"publish_time\"\tTEXT NOT NULL DEFAULT (datetime('now', 'localtime')),\n" +
            "\t\"likeCount\"\tINTEGER NOT NULL DEFAULT 0,\n" +
            "\t\"commentCount\"\tINTEGER NOT NULL DEFAULT 0" +
            ");";

    // 创建 Like 表（避免使用关键字 LIKE）
    private static final String CREATE_Like = "CREATE TABLE \"" + TABLE_NAME_LIKE + "\"(\n" +
            "\t\"id\"\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
            "\t\"video_id\"\tINTEGER NOT NULL," +
            "\t\"user_id\"\tINTEGER NOT NULL," +
            "\t\"like_time\"\tINTEGER NOT NULL DEFAULT (strftime('%s', 'now') * 1000),\n" +
            "\tUNIQUE(video_id, user_id)" +
            ");";

    // 创建 Comment 表
    static final String CREATE_Comment = "CREATE TABLE \"" + TABLE_NAME_COMMENT + "\"(\n" +
            "\t\"id\"\tINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
            "\t\"video_id\"\tINTEGER NOT NULL," +
            "\t\"user_id\"\tINTEGER NOT NULL," +
            "\t\"content\"\tTEXT NOT NULL,\n" +
            "\t\"time\"\tINTEGER NOT NULL DEFAULT (strftime('%s', 'now') * 1000)\n"+
            ");";

    /**
     * 删除表的 SQL 语句（用于数据库升级时重建表结构）
     */
    private static final String DROP_Note = "DROP TABLE IF EXISTS " + TABLE_NAME_Note;
    private static final String DROP_User = "DROP TABLE IF EXISTS " + TABLE_NAME_User;
    private static final String DROP_Video = "DROP TABLE IF EXISTS " + TABLE_NAME_Video;
    private static final String DROP_Like = "DROP TABLE IF EXISTS " + TABLE_NAME_LIKE;
    private static final String DROP_Comment = "DROP TABLE IF EXISTS " + TABLE_NAME_COMMENT;

    /**
     * 软删除标记常量
     * isDeleted 字段使用：1 表示已删除，0 表示未删除
     */
    private static final int HAS_DELETED = 1;
    private static final int UN_DELETED = 0;

    // 上下文引用
    private Context mContext;
    // 可写数据库实例
    private SQLiteDatabase db;
    // 通用键值对容器，用于插入/更新数据
    private ContentValues values = new ContentValues();

    /**
     * 构造函数
     * 初始化数据库帮助类，并获取可写数据库实例
     *
     * @param context  上下文
     * @param name     数据库名
     * @param factory  游标工厂（一般传 null）
     * @param version  数据库版本
     */
    public MySqLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.mContext = context;
        this.db = this.getWritableDatabase();
    }

    /**
     * 当数据库首次创建时调用
     * 执行建表语句
     *
     * @param db 当前数据库实例
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.e("CREATE_Note", CREATE_Note);
        db.execSQL(CREATE_Note);
        Log.e("CREATE_User", CREATE_User);
        db.execSQL(CREATE_User);
        db.execSQL(CREATE_Video);
        db.execSQL(CREATE_Like);
        db.execSQL(CREATE_Comment);
    }

    /**
     * 当数据库版本升级时调用
     * 如果版本低于19，则删除所有表并重新创建（数据会丢失）
     *
     * @param db         当前数据库
     * @param oldVersion 旧版本号
     * @param newVersion 新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 20) {
            // 删除所有表
            db.execSQL(DROP_Video);
            db.execSQL(DROP_Like);
            db.execSQL(DROP_Comment);
            db.execSQL(DROP_User);
            db.execSQL(DROP_Note);

            // 重新创建所有表
            onCreate(db);
        }
    }

    /**
     * 插入一条视频记录
     */
    public Long insertVideo(Long user_id, String videoFilePath, String title, String introduction, String label) {
        values.put("user_id", user_id);
        values.put("videoFilePath", videoFilePath);
        if (title == null || title.trim().isEmpty()) {
            values.putNull("title");
        } else {
            values.put("title", title);
        }
        if (introduction == null || introduction.trim().isEmpty()) {
            values.putNull("introduction");
        } else {
            values.put("introduction", introduction);
        }
        if (label == null || label.trim().isEmpty()) {
            values.putNull("label");
        } else {
            values.put("label", label);
        }

        Long id = this.db.insert(TABLE_NAME_Video, null, values);
        values.clear();
        return id;
    }

    /**
     * 插入初始测试视频数据（仅用于开发测试）
     */
    public void insertTestVideos() {
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME_Video, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();

        if (count > 0) {
            Log.d("DB", "视频表已有数据，跳过插入测试数据");
            return;
        }

        Object[][] testData = {
                {1L, 0, 0, "城市骑行日记-穿越都市的风",
                        "第一视角骑行穿越城市街道，感受速度与风景的交织。视频采用高清1080p拍摄，适合在通勤路上放松心情。",
                        "#骑行,#城市风光,#vlog", 2L, "https://storage.googleapis.com/gtv-videos-bucket/sample/ForBiggerJoyrides.mp4"},

                {2L, 0, 0, "火焰之舞：每一帧都是艺术",
                        "慢镜头下的火焰燃烧全过程，光影交错，细节拉满。这不仅是一段视频，更是一场视觉盛宴。",
                        "#火焰艺术,#HDR,#摄影", 3L, "https://storage.googleapis.com/gtv-videos-bucket/sample/ForBiggerBlazes.mp4"},

                {3L, 0, 0, "开源动画的起点：《大象的梦》完整版",
                        "2006年，Blender基金会用完全开源的工具制作了这部3D动画短片——《大象的梦》。它是数字艺术史上的里程碑，证明了免费软件也能创造电影级作品。",
                        "#开源,#3D动画,#Blender", 4L, "https://storage.googleapis.com/gtv-videos-bucket/sample/ElephantsDream.mp4"},

                {4L, 0, 0, "飞向山海：给疲惫心灵的一次深呼吸",
                        "当城市喧嚣让人窒息，不如让眼睛先去旅行。这段航拍带你穿越云层，俯瞰大地脉络。闭上眼，深呼吸，世界依然辽阔。",
                        "#治愈系风景,#航拍中国,#逃离城市", 5L, "https://storage.googleapis.com/gtv-videos-bucket/sample/ForBiggerEscapes.mp4"}
        };

        for (Object[] data : testData) {
            long id = (Long) data[0];
            int likeCount = (Integer) data[1];
            int commentCount = (Integer) data[2];
            String title = (String) data[3];
            String intro = (String) data[4];
            String label = (String) data[5];
            long userId = (Long) data[6];
            String videoPath = (String) data[7];

            insertVideo(userId, videoPath, title, intro, label);
            Log.d("DB", "插入测试视频: " + title);
        }
    }

    /**
     * 插入初始测试用户数据（仅用于开发测试）
     */
    public void insertTestUsers() {
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME_User, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();

        if (count > 1) {
            return;
        }

        insertUser("City Wanderer", "123456xP", "骑行爱好者", "https://picsum.photos/100");
        insertUser("Poet of Light and Shadow", "123456xP", "热爱光影艺术", "https://picsum.photos/101");
        insertUser("Digital Art Archive", "123456xP", "开源动画收藏", "https://picsum.photos/102");
        insertUser("A Mindful Travel Guide", "123456xP", "治愈系内容创作者", "https://picsum.photos/103");
    }

    /**
     * 插入初始测试点赞数据（仅用于开发测试）
     */
    public void insertTestLikes() {
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME_LIKE, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();

        if (count > 0) {
            Log.d("DB", "Like表已有数据，跳过插入测试点赞数据");
            return;
        }

        long[][] testLikeData = {
                {1L, 2L}, {2L, 2L}, {3L, 2L},
                {1L, 3L}, {2L, 3L}, {3L, 3L}, {4L, 3L},
                {2L, 4L}, {3L, 4L}, {4L, 4L},
                {1L, 5L}, {2L, 5L}, {3L, 5L}, {4L, 5L},
        };

        for (long[] data : testLikeData) {
            long videoId = data[0];
            long userId = data[1];
            toggleLike(videoId, userId);
            Log.d("DB", "用户 " + userId + " 点赞了视频 " + videoId);
        }
    }

    /**
     * 获取所有视频
     */
    public ArrayList<Video> getAllVideos() throws Exception {
        ArrayList<Video> list = new ArrayList<>();
        String sql = "SELECT v.*, u.name as realName, u.avatarPath as userAvatarUrl " +
                "FROM " + TABLE_NAME_Video + " v " +
                "LEFT JOIN " + TABLE_NAME_User + " u ON v.user_id = u.id ";

        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") long id = cursor.getLong(cursor.getColumnIndex("id"));
                @SuppressLint("Range") long userId = cursor.getLong(cursor.getColumnIndex("user_id"));
                @SuppressLint("Range") String url = cursor.getString(cursor.getColumnIndex("videoFilePath"));
                @SuppressLint("Range") int likeCount = cursor.getInt(cursor.getColumnIndex("likeCount"));
                @SuppressLint("Range") int commentCount = cursor.getInt(cursor.getColumnIndex("commentCount"));
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex("title"));
                @SuppressLint("Range") String intro = cursor.getString(cursor.getColumnIndex("introduction"));
                @SuppressLint("Range") String label = cursor.getString(cursor.getColumnIndex("label"));
                @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("realName"));
                @SuppressLint("Range") String avatarUrl = cursor.getString(cursor.getColumnIndex("userAvatarUrl"));

                User user = new User(this, userId, userName, "");
                user.setAvatarPath(avatarUrl);

                Video video = new Video(id, likeCount, commentCount, title, intro, label, avatarUrl, userName, userId, user, url);
                list.add(video);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    /**
     * 切换点赞状态
     */
    public boolean toggleLike(long videoId, long userId) {
        if (hasLiked(videoId, userId)) {
            db.delete(TABLE_NAME_LIKE, "video_id=? AND user_id=?", new String[]{String.valueOf(videoId), String.valueOf(userId)});
            db.execSQL("UPDATE " + TABLE_NAME_Video + " SET likeCount = likeCount - 1 WHERE id = ?", new Object[]{videoId});
            return false;
        } else {
            values.put("video_id", videoId);
            values.put("user_id", userId);
            db.insert(TABLE_NAME_LIKE, null, values);
            values.clear();
            db.execSQL("UPDATE " + TABLE_NAME_Video + " SET likeCount = likeCount + 1 WHERE id = ?", new Object[]{videoId});
            return true;
        }
    }

    /**
     * 检查是否已点赞
     */
    public boolean hasLiked(long videoId, long userId) {
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME_LIKE + " WHERE video_id=? AND user_id=?", new String[]{String.valueOf(videoId), String.valueOf(userId)});
        boolean liked = cursor.getCount() > 0;
        cursor.close();
        return liked;
    }
    /**
     * 插入一条评论
     * @param videoId 视频ID
     * @param userId 用户ID
     * @param content 评论内容
     * @return 返回新评论的 ID，失败返回 -1
     */
    public long insertComment(long videoId, long userId, String content) {
        if (content == null || content.trim().isEmpty()) {
            throw new IllegalArgumentException("评论内容不能为空");
        }
        ContentValues values = new ContentValues();
        values.put("video_id", videoId);
        values.put("user_id", userId);
        values.put("content", content);
        // time 字段使用默认值（自动插入当前时间戳）
        return db.insert(TABLE_NAME_COMMENT, null, values);
    }
    /**
     * 获取某个视频的所有评论（附带用户名和头像）
     */
    public List<Comment> getCommentsByVideoId(long videoId) {
        List<Comment> comments = new ArrayList<>();
        // 修改SQL查询，每次都从用户表获取最新数据
        String sql = "SELECT c.id, c.video_id, c.user_id, c.content, c.time, u.name, u.avatarPath " +
                "FROM " + TABLE_NAME_COMMENT + " c " +
                "JOIN " + TABLE_NAME_User + " u ON c.user_id = u.id " +
                "WHERE c.video_id = ? " +
                "ORDER BY c.time DESC";

        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(videoId)});
        while (cursor.moveToNext()) {
            @SuppressLint("Range") long id = cursor.getLong(cursor.getColumnIndex("id"));
            @SuppressLint("Range") long commentVideoId = cursor.getLong(cursor.getColumnIndex("video_id"));
            @SuppressLint("Range") long userId = cursor.getLong(cursor.getColumnIndex("user_id"));
            @SuppressLint("Range") String content = cursor.getString(cursor.getColumnIndex("content"));
            @SuppressLint("Range") long time = cursor.getLong(cursor.getColumnIndex("time"));
            @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("name"));
            @SuppressLint("Range") String userAvatarUrl = cursor.getString(cursor.getColumnIndex("avatarPath"));

            Comment comment = new Comment(id, commentVideoId, userId, content, time, userName, userAvatarUrl);
            comments.add(comment);
        }
        cursor.close();
        return comments;
    }
    /**
     * 更新视频的评论数
     */
    public void updateVideoCommentCount(long videoId) {
        String sql = "UPDATE " + TABLE_NAME_Video +
                " SET commentCount = (" +
                "   SELECT COUNT(*) FROM " + TABLE_NAME_COMMENT +
                "   WHERE video_id = ?" +
                ") WHERE id = ?";
        db.execSQL(sql, new Object[]{videoId, videoId});
    }
    /**
     * 获取视频的评论数
     */
    public int getVideoCommentCount(long videoId) {
        Cursor cursor = db.rawQuery("SELECT commentCount FROM " + TABLE_NAME_Video + " WHERE id=?", new String[]{String.valueOf(videoId)});
        int commentCount = 0;
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") int count = cursor.getInt(cursor.getColumnIndex("commentCount"));
            commentCount = count;
        }
        cursor.close();
        return commentCount;
    }
    /**
     * 获取视频的点赞数
     */
    public int getVideoLikeCount(long videoId) {
        Cursor cursor = db.rawQuery("SELECT likeCount FROM " + TABLE_NAME_Video + " WHERE id=?", new String[]{String.valueOf(videoId)});
        int likeCount = 0;
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") int count = cursor.getInt(cursor.getColumnIndex("likeCount"));
            likeCount = count;
        }
        cursor.close();
        return likeCount;
    }

    /**
     * 根据用户获取其所有视频列表
     */
    public ArrayList<Video> getAllVideosByUser(User user) throws IOException {
        ArrayList<Video> videoArrayList = new ArrayList<>();
        Long userId = user.getId();
        String SQL_CMD = "SELECT v.*, u.name as realName, u.avatarPath as userAvatarUrl " +
                "FROM " + TABLE_NAME_Video + " v " +
                "LEFT JOIN " + TABLE_NAME_User + " u ON v.user_id = u.id " +
                "WHERE v.user_id = ?";

        Cursor cursor = db.rawQuery(SQL_CMD, new String[]{String.valueOf(userId)});
        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") long id = cursor.getLong(cursor.getColumnIndex("id"));
                @SuppressLint("Range") long vidUserId = cursor.getLong(cursor.getColumnIndex("user_id"));
                @SuppressLint("Range") String videoFilePath = cursor.getString(cursor.getColumnIndex("videoFilePath"));
                @SuppressLint("Range") int likeCount = cursor.getInt(cursor.getColumnIndex("likeCount"));
                @SuppressLint("Range") int commentCount = cursor.getInt(cursor.getColumnIndex("commentCount"));
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex("title"));
                @SuppressLint("Range") String introduction = cursor.getString(cursor.getColumnIndex("introduction"));
                @SuppressLint("Range") String label = cursor.getString(cursor.getColumnIndex("label"));
                @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("realName"));
                @SuppressLint("Range") String userAvatarUrl = cursor.getString(cursor.getColumnIndex("userAvatarUrl"));

                Video video = new Video(id, likeCount, commentCount, title, introduction, label, userAvatarUrl, userName, vidUserId, user, videoFilePath);
                videoArrayList.add(video);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return videoArrayList;
    }

    /**
     * 插入一个新用户（支持头像路径）
     */
    public Long insertUser(String username, String password, String selfIntroduction, String avatarPath) {
        values.put("name", username);
        values.put("password", password);
        values.put("isDeleted", UN_DELETED);

        if (selfIntroduction == null || selfIntroduction.trim().isEmpty()) {
            selfIntroduction = "设置你的个人简介吧！";
        }
        values.put("selfIntroduction", selfIntroduction);

        if (avatarPath != null) {
            values.put("avatarPath", avatarPath);
        } else {
            values.putNull("avatarPath");
        }

        Long id = this.db.insert(TABLE_NAME_User, null, values);
        Toast.makeText(mContext, "新增了一个User!id=" + id, Toast.LENGTH_SHORT).show();
        values.clear();
        return id;
    }

    /**
     * 更新用户信息（用户名、密码、个人简介）
     */
    public void updateUser(Long id, String username, String password,
                           String selfIntroduction, String newAvatarPath) {
        values.put("name", username);
        values.put("password", password);

        if (selfIntroduction == null || selfIntroduction.trim().isEmpty()) {
            selfIntroduction = "设置你的个人简介吧！";
        }
        values.put("selfIntroduction", selfIntroduction);

        if (newAvatarPath != null) {
            values.put("avatarPath", newAvatarPath);
        } else {
            values.putNull("avatarPath");
        }

        values.put("isDeleted", UN_DELETED);

        int res = db.update(TABLE_NAME_User, values, "id=?", new String[]{String.valueOf(id)});
        Toast.makeText(mContext, "更新用户信息：" + res + " 条记录", Toast.LENGTH_SHORT).show();
        values.clear();
    }

    /**
     * 【硬删除】从数据库中彻底删除一个用户
     */
    public void deleteUser(int id) {
        db.delete(TABLE_NAME_User, "id=?", new String[]{String.valueOf(id)});
        Toast.makeText(mContext, "硬删除一个用户success!", Toast.LENGTH_LONG).show();
    }

    /**
     * 【软删除】将用户标记为已删除
     */
    public void pretendToDelete(User user) {
        values.put("name", user.getName());
        values.put("password", user.getPassword());
        values.put("isDeleted", HAS_DELETED);
        int res = db.update(TABLE_NAME_User, values, "id=?", new String[]{String.valueOf(user.getId())});
        Toast.makeText(mContext, "软删除了一个User信息!？res=" + res + "id=" + String.valueOf(user.getId()), Toast.LENGTH_SHORT).show();
        values.clear();
    }

    /**
     * 【硬删除】从数据库中删除一条笔记
     */
    public void deleteNote(Long id) {
        db.delete(TABLE_NAME_Note, "id=?", new String[]{String.valueOf(id)});
        Toast.makeText(mContext, "软删除一个Note success!", Toast.LENGTH_LONG).show();
    }

    /**
     * 根据用户名查询用户信息
     */
    @SuppressLint("Range")
    public User getUserByName(String username) {
        Log.d("DB", "正在查询用户: " + username);
        String SQL_CMD = "SELECT * FROM \"" + TABLE_NAME_User + "\" WHERE name=? AND isDeleted=0;";
        Cursor cursor = db.rawQuery(SQL_CMD, new String[]{username});
        User user = null;

        if (cursor.moveToFirst()) {
            Long id = cursor.getLong(cursor.getColumnIndex("id"));
            String password = cursor.getString(cursor.getColumnIndex("password"));
            String intro = cursor.getString(cursor.getColumnIndex("selfIntroduction"));
            String avatarPath = cursor.getString(cursor.getColumnIndex("avatarPath"));

            if (intro == null || intro.trim().isEmpty()) {
                intro = "设置你的个人简介吧！";
            }

            user = new User(this, id, username, password);
            user.setSelfIntroduction(intro);
            user.setAvatarPath(avatarPath);
        }

        cursor.close();
        return user;
    }

    public Context getContext() {
        return mContext;
    }

    /**
     * 根据标签搜索视频
     * @param keyword 搜索关键词（如"风景"、"骑行"等）
     * @return 匹配的视频列表
     */
    public List<Video> searchVideosByLabel(String keyword) {
        List<Video> searchResults = new ArrayList<>();

        if (keyword == null || keyword.trim().isEmpty()) {
            return searchResults;
        }

        // 处理搜索关键词：去除前后空格
        String searchTerm = keyword.trim();

        // SQL查询：在label字段中搜索包含关键词的视频
        // label格式如："#骑行,#城市风光,#vlog"
        // 使用LOWER进行不区分大小写搜索，匹配包含关键词的标签
//        排序规则为：
//        第一优先级：点赞数 (v.likeCount DESC) - 从高到低
//        第二优先级：评论数 (v.commentCount DESC) - 从高到低
//        第三优先级：发布时间 (v.publish_time DESC) - 最新的在前
        String sql = "SELECT v.*, u.name as realName, u.avatarPath as userAvatarUrl " +
                "FROM " + TABLE_NAME_Video + " v " +
                "LEFT JOIN " + TABLE_NAME_User + " u ON v.user_id = u.id " +
                "WHERE LOWER(v.label) LIKE ? " +
                "ORDER BY v.likeCount DESC, v.commentCount DESC, v.publish_time DESC";

        // 使用%通配符进行模糊匹配，搜索包含关键词的标签
        // 例如：搜索"风景"会匹配"#治愈系风景"、"#城市风光"等
        String[] selectionArgs = new String[]{"%" + searchTerm.toLowerCase() + "%"};

        Cursor cursor = db.rawQuery(sql, selectionArgs);

        try {
            if (cursor.moveToFirst()) {
                do {
                    @SuppressLint("Range") long id = cursor.getLong(cursor.getColumnIndex("id"));
                    @SuppressLint("Range") long userId = cursor.getLong(cursor.getColumnIndex("user_id"));
                    @SuppressLint("Range") String url = cursor.getString(cursor.getColumnIndex("videoFilePath"));
                    @SuppressLint("Range") int likeCount = cursor.getInt(cursor.getColumnIndex("likeCount"));
                    @SuppressLint("Range") int commentCount = cursor.getInt(cursor.getColumnIndex("commentCount"));
                    @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex("title"));
                    @SuppressLint("Range") String intro = cursor.getString(cursor.getColumnIndex("introduction"));
                    @SuppressLint("Range") String label = cursor.getString(cursor.getColumnIndex("label"));
                    @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("realName"));
                    @SuppressLint("Range") String avatarUrl = cursor.getString(cursor.getColumnIndex("userAvatarUrl"));

                    User user = new User(this, userId, userName, "");
                    user.setAvatarPath(avatarUrl);

                    Video video = new Video(id, likeCount, commentCount, title, intro, label,
                            avatarUrl, userName, userId, user, url);
                    searchResults.add(video);

                } while (cursor.moveToNext());
            }
        } finally {
            cursor.close();
        }

        Log.d("DB", "搜索关键词: " + keyword + ", 找到 " + searchResults.size() + " 个结果");
        return searchResults;
    }

    /**
     * 获取指定用户点赞过的所有视频
     */
    public ArrayList<Video> getLikedVideosByUser(User user) throws IOException {
        ArrayList<Video> list = new ArrayList<>();

        String sql = "SELECT v.*, u.name as realName, u.avatarPath as userAvatarUrl " +
                "FROM " + TABLE_NAME_Video + " v " +
                "LEFT JOIN " + TABLE_NAME_User + " u ON v.user_id = u.id " +
                "INNER JOIN " + TABLE_NAME_LIKE + " l ON v.id = l.video_id " +
                "WHERE l.user_id = ?";

        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(user.getId())});

        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") long id = cursor.getLong(cursor.getColumnIndex("id"));
                @SuppressLint("Range") long userId = cursor.getLong(cursor.getColumnIndex("user_id"));
                @SuppressLint("Range") String videoFilePath = cursor.getString(cursor.getColumnIndex("videoFilePath"));
                @SuppressLint("Range") int likeCount = cursor.getInt(cursor.getColumnIndex("likeCount"));
                @SuppressLint("Range") int commentCount = cursor.getInt(cursor.getColumnIndex("commentCount"));
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex("title"));
                @SuppressLint("Range") String introduction = cursor.getString(cursor.getColumnIndex("introduction"));
                @SuppressLint("Range") String label = cursor.getString(cursor.getColumnIndex("label"));
                @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("realName"));
                @SuppressLint("Range") String userAvatarUrl = cursor.getString(cursor.getColumnIndex("userAvatarUrl"));

                User videoOwner = new User(this, userId, userName, "");
                videoOwner.setAvatarPath(userAvatarUrl);

                Video video = new Video(id, likeCount, commentCount, title, introduction, label,
                        userAvatarUrl, userName, userId, videoOwner, videoFilePath);
                list.add(video);
            } while (cursor.moveToNext());
        }

        cursor.close();
        return list;
    }

    /**
     * 获取用户作品总获赞数
     * @param userId
     * @return
     */
    /**
     * 获取用户作品总获赞数
     * @param userId 用户ID
     * @return 总获赞数
     */
    public int getTotalLikesForUserVideos(long userId) {
        // 修正SQL查询：从Video表中获取该用户所有视频的likeCount总和
        String query = "SELECT SUM(likeCount) FROM " + TABLE_NAME_Video + " WHERE user_id = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)});

        int totalLikes = 0;
        if (cursor.moveToFirst()) {
            totalLikes = cursor.getInt(0);
        }

        cursor.close();
        Log.d("DB", "用户 " + userId + " 的作品总获赞数: " + totalLikes);
        return totalLikes;
    }
    /**
     * 更新视频信息
     */
    public boolean updateVideoInfo(long videoId, String title, String introduction, String label) {
        ContentValues values = new ContentValues();
        values.put("title", title);
        values.put("introduction", introduction);
        values.put("label", label);

        int rowsAffected = db.update(TABLE_NAME_Video, values,
                "id = ?", new String[]{String.valueOf(videoId)});

        Log.d("DB", "更新视频信息，ID: " + videoId + ", 影响行数: " + rowsAffected);
        return rowsAffected > 0;
    }

    /**
     * 删除视频
     */
    public boolean deleteVideo(long videoId) {
        SQLiteDatabase db = this.getWritableDatabase();

        try {
            db.beginTransaction();

            // 删除相关的评论
            db.delete(TABLE_NAME_COMMENT, "video_id = ?",
                    new String[]{String.valueOf(videoId)});

            // 删除相关的点赞
            db.delete(TABLE_NAME_LIKE, "video_id = ?",
                    new String[]{String.valueOf(videoId)});

            // 删除视频
            int rowsAffected = db.delete(TABLE_NAME_Video,
                    "id = ?", new String[]{String.valueOf(videoId)});

            db.setTransactionSuccessful();
            Log.d("DB", "删除视频成功，ID: " + videoId + ", 影响行数: " + rowsAffected);
            return rowsAffected > 0;

        } catch (Exception e) {
            Log.e("DB", "删除视频失败", e);
            return false;
        } finally {
            db.endTransaction();
        }

}
    /**
     * 根据ID获取视频
     */
    /**
     * 根据ID获取视频
     */
    @SuppressLint("Range")
    public Video getVideoById(long videoId) {
        String sql = "SELECT v.*, u.name as realName, u.avatarPath as userAvatarUrl " +
                "FROM " + TABLE_NAME_Video + " v " +
                "LEFT JOIN " + TABLE_NAME_User + " u ON v.user_id = u.id " +
                "WHERE v.id = ?";

        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(videoId)});
        Video video = null;

        if (cursor != null && cursor.moveToFirst()) {
            long id = cursor.getLong(cursor.getColumnIndex("id"));
            long userId = cursor.getLong(cursor.getColumnIndex("user_id"));
            String videoFilePath = cursor.getString(cursor.getColumnIndex("videoFilePath"));
            int likeCount = cursor.getInt(cursor.getColumnIndex("likeCount"));
            int commentCount = cursor.getInt(cursor.getColumnIndex("commentCount"));
            String title = cursor.getString(cursor.getColumnIndex("title"));
            String introduction = cursor.getString(cursor.getColumnIndex("introduction"));
            String label = cursor.getString(cursor.getColumnIndex("label"));
            String userName = cursor.getString(cursor.getColumnIndex("realName"));
            String userAvatarUrl = cursor.getString(cursor.getColumnIndex("userAvatarUrl"));

            User user = new User(this, userId, userName, "");
            user.setAvatarPath(userAvatarUrl);

            video = new Video(id, likeCount, commentCount, title, introduction, label,
                    userAvatarUrl, userName, userId, user, videoFilePath);
        }

        if (cursor != null) {
            cursor.close();
        }
        return video;
    }
}
